<template>
  <div class="excelFn">
    <span>
      <input ref="fileInput" type="file" accept=".xls,.xlsx,.csv" @change="fileChangeHandle">
      <!-- <el-button type="primary" @click="clickHandle">
        <el-icon>
          <Document />
        </el-icon>
        导入Excel
      </el-button> -->
    </span>
    <h2></h2>
    <el-table class="crmonepage-hello" ref="tabled" :data="list" style="width: 100%" :span-method="dataArraySpanMethod">
      <el-table-column v-for="(col, index) in cols" :label="col.label" :key="index" :prop="col.prop">
        <el-table-column v-for="(props, propsindex) in col.propsChildren" :label="props.label" :key="propsindex"
          :prop="props.prop"></el-table-column>
      </el-table-column>
    </el-table>
    <!-- <button @click="exportExcel"></button> -->



  </div>
</template>

<script lang="ts" setup>
import { rejects } from 'assert'
import { TableColumnCtx } from 'element-plus/es/components/table/src/table-column/defaults'
import { resolve } from 'path'
import { ref, reactive, toRefs, onBeforeMount, onBeforeUnmount, onMounted, InputHTMLAttributes } from 'vue'

import * as XLSX from 'xlsx'
//import { useStore,mapState } from 'vuex'
//let store = useStore()

onBeforeMount(() => { })
onMounted(() => { })
onBeforeUnmount(() => { })




let tableHeadColNum = ref(3)
let tableHeadData = ref<object[]>([])

let fileInput = ref()
// const fileChangeHandle = (event: Event) => {
//   // 1 获取文件对象
//   let input = event.target as HTMLInputElement
//   // 2 获取文件数据
//   let file = input.files ? input.files[0] : null

//   // 3判断
//   if (!file) return

//   // // 1 检查文件类型
//   // const fileExtension= (file.name as any).pop().toLowerCase()
//   // if(fileExtension!='xlsx'){}

//   // 2 cosnt fileReader=  new FileReader()  Web应用程序异步读取存储在用户计算机上的文件，创建FileReader对象，读取文件
//   const fileReader = new FileReader()

//   // 3 读取文件的内容为二进制
//   // fileReader.readAsBinaryString(file)

//   // 4 fileReader.onload 监听读取文件成功
//   fileReader.onload = (e) => {

//     // const workbook = XLSX.read(e.target?.result, { type: 'binary' })
//     // const workbook = XLSX.read(data, { type: 'array' })
//     const data = new Uint8Array(e.target?.result as ArrayBuffer)
//     const workbook = XLSX.read(data, { type: 'array' })



//     const fistSheetName = workbook.SheetNames[0]
//     const workSheet = workbook.Sheets[fistSheetName]
//     // const jsonData = XLSX.utils.sheet_to_json(workSheet, { header: 1, defval: null })
//     // console.log('jsonData', workbook, workSheet, jsonData);
//   }
//   fileReader.readAsArrayBuffer(file);




// }

/**
 * 读取上传文件
 * @param file 
 */
const readFile = (file: File) => {
  return new Promise((resolve, reject) => {
    let reader = new FileReader()
    // 把文件转为二进制
    reader.readAsArrayBuffer(file)
    reader.onload = e => {
      resolve(e.target?.result)
    }
  })
}

/**
 * 获取表头信息
 * @param sheet 
 */
const getHeaderRow = (sheet: XLSX.WorkSheet) => {
  const headers = [];
  //获取总共的行数和列数
  let ref = sheet["!ref"] || ''
  const range = XLSX.utils.decode_range(ref)
  const R = range.s.r; // 开始行
  //遍历每列
  for (let C = range.s.c; C <= range.e.c; ++C) {
    // 找到每列的第一行数据即使表头
    const cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })];
    let hdr = "UNKNOWN " + C;
    if (cell && cell.t) hdr = XLSX.utils.format_cell(cell);
    headers.push(hdr);
  }

  console.log('headers', headers);

  return headers;
}


/**
 * 初始合并单元格
 * https://blog.csdn.net/u014804332/article/details/120763097
 * excel合并单元格后默认只有左上角的单元格保留值 此方法会将所有合并的单元格都赋值
 */
let mergeMap = ref<any[]>([])
const initMerge = (workSheet: XLSX.WorkSheet) => {
  const merges = workSheet['!merges'];
  if (!merges) return
  merges.forEach(p => {
    let columnIndex = p['s']['c']
    const endColumn = p['e']['c']
    let rowIndex = p['s']['r']
    const endRow = p['e']['r']
    const startColumn = createCol(p['s']['c'])
    const startRow = p['s']['r'] + 1
    while (columnIndex <= endColumn) {
      rowIndex = p['s']['r']
      while (rowIndex <= endRow) {
        let column = createCol(columnIndex);
        let row = rowIndex + 1
        mergeMap.value[column + row] = workSheet[startColumn + startRow]['v']
        rowIndex++
      }
      columnIndex++
    }

  })
}

/**
 * 原https://blog.csdn.net/u014804332/article/details/120763097
 * @param n 
 */
function createCol(n: any) {
  const ordA = 'A'.charCodeAt(0)
  const ordZ = 'Z'.charCodeAt(0)
  const len = ordZ - ordA + 1
  let str = ""
  while (n >= 0) {
    str = String.fromCharCode(n % len + ordA) + str
    n = Math.floor(n / len) - 1
  }
  return str
}


const fileChangeHandle = async (event: Event) => {
  // 1 获取文件对象
  let input = event.target as HTMLInputElement
  // 2 获取文件数据
  let file = input.files ? input.files[0] : null

  // 3 判断文件是否获取成功
  if (!file) return

  // 4 检查文件类型：https://blog.csdn.net/weixin_39277183/article/details/115530518
  // const fileExtension = (file.name as any).pop().toLowerCase()
  const fileExtension = file.name.split(".")[1];
  const fileType = ["xlsx", "xlc", "xlm", "xls", "xlt", "xlw", "csv"].some(
    item => item === fileExtension
  );
  if (!fileType) { alert("格式错误！请重新选择"); return; }

  // 5 读取文件内容
  let excelData = await readFile(file)

  // 6 使用XLSX读取二进制数据 生成excel的全部信息
  var workbook = XLSX.read(excelData, { type: "binary" });

  // 7 通过XLSX转换为可读的表格数据
  // 7.1 fistSheetName = workbook.SheetNames[0] 表格中文名称
  // 7.2 workbook.Sheets[fistSheetName] 上传excel的第一个sheet的数据
  const fistSheetName = workbook.SheetNames[0]
  const workSheet = workbook.Sheets[fistSheetName]
  // XLSX.utils.sheet_to_json()方法将工作表转换为JSON格式时，
  /**
   * XLSX.utils.sheet_to_json(workSheet) 将工作表转换为JSON格式
   * 默认情况下会忽略表格的第一行，将其视为表头信息而非数据。
   * 如果需要第一行数据，在调用该方法时设置参数{ header: 1 }，这样会将第一行数据包含在结果中。
   * { defval:''} 使用指定值代替 null 或未定义
   */
  let data = XLSX.utils.sheet_to_json(workSheet);
  let data1 = XLSX.utils.sheet_to_json(workSheet, { header: 1, defval: '' });
  let dataA = XLSX.utils.sheet_to_json(workSheet, { header: 'A', defval: '' });
  console.log('workSheet', data, data1, dataA);
  const header = getHeaderRow(workSheet);
  initMerge(workSheet) //初始化合并单元格信息
  convertHeader(workSheet, 3)
  // tableData = data;
  // tableHeader = header;


}

const convertHeader = (workSheet: XLSX.WorkSheet, num: number) => {
  console.log(1111, workSheet);

  // 获取表的有效范围
  // worksheet['!ref'] // 返回 'A1:B20'
  let range = workSheet['!range'] // 返回 range 对象，{ s: { r: 0, c: 0}, e: { r: 100, c: 2 } }
  const merges = workSheet['!merges'];
  if (!merges) return



  merges.forEach(p => {
    let columnIndex = p['s']['c']
    let endColumn = p['e']['c']
    // let rowIndex = p['s']['r']
    let endRow = p['e']['r']
    let startColumn = createCol(p['s']['c'])
    let startRow = p['s']['r'] + 1

    let colStart = p['s']['c']
    let colEnd = p['e']['c']
    let colIndex = p['s']['c']
    let rowStart = p['s']['r']
    let rowEnd = p['e']['r']
    let rowIndex = p['s']['r']
    console.log(`colStart:${colStart} colEnd:${colEnd} colIndex:${colIndex} rowStart:${rowStart} rowEnd:${rowEnd} rowIndex:${rowIndex}`,);
    // while (columnIndex <= endColumn) {
    //   rowIndex = p['s']['r']
    //   while (rowIndex <= endRow) {
    //     let column = createCol(columnIndex);
    //     let row = rowIndex + 1
    //     mergeMap.value[column + row] = workSheet[startColumn + startRow]['v']
    //     rowIndex++
    //   }
    //   columnIndex++
    // }

  })

  // console.log('workSheet-' + num, mergeMap.value,);

}

// ==========================================               ==========================================
const list = ref([
  { aQty: "1", bQty: "8", cQty: "2", dQty: "-1", eQty: "11", brand: "一点点", brand2: "列合并测试", brand3: "测试" },
  { aQty: "3", bQty: "2", cQty: "12", dQty: "-3", eQty: "121", brand: "一点点", brand2: "列合并测试", brand3: "测试" },
  { aQty: "13", bQty: "2", cQty: "24", dQty: "-1", eQty: "11", brand: "点点", brand2: "行合并", brand3: "测试" }
])
// 表头数组--列为单位
const cols = ref([
  {
    prop: "brand", label: "面包店", width: null,
    propsChildren: [{ prop: "aQty", label: "西瓜汁", width: null, }]
  },

  // { prop: "brand2", label: "列合并测试", width: null },
  // { prop: "brand3", label: "测试面包店", width: null },
  // { prop: "Production", label: "饮料", width: null, propsChildren: [{ prop: "aQty", label: "西瓜汁", width: null, }] },
  // {
  //   prop: "market", label: "食物数量", width: null, propsChildren: [
  //     { prop: "bQty", label: "菜包", width: 150 },
  //     { prop: "cQty", label: "肉包", width: 150 },
  //     { prop: "dQty", label: "苹果", width: 150 },
  //     { prop: "eQty", label: "西瓜", width: 150 }]
  // },
])

// 合并列数数组
const spanArr = ref([])
const spanArr2 = ref([])
const pos = ref(0)
const pos2 = ref(0)


interface tableType {
  row: any;
  rowIndex: number;
  column: TableColumnCtx<any>;
  columnIndex: number;
}

const dataArraySpanMethod = ({ row, column, rowIndex, columnIndex }: tableType) => {
  return []
}  // 因为dataArraySpanMethod属性定义的是函数




// // 1 获取文件对象
// let input = event.target as HTMLInputElement
// // 2 获取文件数据
// let file = input.files ? input.files[0] : null

// // 3判断是否获取到文件数据
// if (!file) return

// // 4 检查文件类型
// const fileExtension = (file.name as any).pop().toLowerCase()
// if (fileExtension != 'xlsx') { }

// // 5 创建FileReader对象，Web应用程序异步读取存储在用户计算机上的文件
// const fileReader = new FileReader()

// // 6 读取文件的内容为二进制 fileReader.readAsBinaryString(file) 已弃用
// fileReader.readAsArrayBuffer(file);

// // 7 fileReader.onload 监听读取文件成功
// fileReader.onload = (e) => {

//   const workbook = XLSX.read(e.target?.result, { type: 'binary' })
//   // const workbook = XLSX.read(data, { type: 'array' })
//   const fistSheetName = workbook.SheetNames[0]
//   const workSheet = workbook.Sheets[fistSheetName]
//   const jsonData = XLSX.utils.sheet_to_json(workSheet, { header: 1, defval: null })
//   console.log('jsonData', workbook, workSheet, jsonData);
// }




</script>

<style scoped lang='scss'>
.excelFn {
  width: 100%;
  height: 100%;
  padding: 20px;
}
</style>